<?php

require_once('entity/Topic.class.php');
require_once('persistence/AbstractGenericDao.class.php');
require_once('persistence/ITopicDao.class.php');

define('_SQL_FETCH_OFFERED_TOPICS', <<<EOD
SELECT
  ot.offered_topic_id AS offered_topic_id,
  t.topic_id AS topic_id,
  t.title AS title,
  t.created AS created,
  COUNT(v.topic_id) AS vote_cnt,
  (:user_id IN (SELECT vv.user_id FROM vote AS vv WHERE vv.topic_id=t.topic_id)) AS yet_voted,
  ot.offered_category_id AS offered_category_id,
  t.user_id AS user_id
FROM
  topic t
INNER JOIN
  offered_topic AS ot ON t.topic_id=ot.topic_id
LEFT OUTER JOIN
  vote v ON t.topic_id=v.topic_id
GROUP BY
  t.topic_id
EOD
);


define('_SQL_FETCH_NEEDED_TOPICS', <<<EOD
SELECT
  nt.needed_topic_id AS needed_topic_id,
  t.topic_id AS topic_id,
  t.title AS title,
  t.created AS created,
  COUNT(v.topic_id) AS vote_cnt,
  (:user_id IN (SELECT vv.user_id FROM vote AS vv WHERE vv.topic_id=t.topic_id)) AS yet_voted,
  nt.needed_category_id AS needed_category_id,
  t.user_id AS user_id

FROM
  topic t
INNER JOIN
  needed_topic AS nt ON t.topic_id=nt.topic_id
LEFT OUTER JOIN
  vote v ON t.topic_id=v.topic_id
GROUP BY
  t.topic_id
EOD
);


DEFINE('_SQL_INSERT_TOPIC', 'INSERT INTO ' . TBL_TOPIC . ' (user_id, title, created) VALUES (:user_id, :title, :created)');
DEFINE('_SQL_INSERT_OFFERED_TOPIC', 'INSERT INTO ' . TBL_OFFERED_TOPIC . ' (topic_id, offered_category_id) VALUES (:topic_id, :offered_category_id)');
DEFINE('_SQL_INSERT_NEEDED_TOPIC', 'INSERT INTO ' . TBL_NEEDED_TOPIC . ' (topic_id, needed_category_id) VALUES (:topic_id, :needed_category_id)');


class GenericTopicDao extends AbstractGenericDao implements ITopicDao {

	
	public function GenericTopicDao($db) {
		parent::__construct($db);
	}
	
	public function fetchOfferedTopics($userId)  {
		$stmt = $this->prepareStatement(_SQL_FETCH_OFFERED_TOPICS);
		
		$stmt->bindParam(':user_id', $userId, PDO::PARAM_INT);
		$stmt->execute();
		
		$topics = array();
		$rows = $stmt->fetchAll();
		foreach($rows as $row) {
			array_push($topics,
				new OfferedTopic(
					$row['topic_id'], $row['user_id'], $row['title'], $row['created'], $row['vote_cnt'], ($row['yet_voted']==1?true:false),
					$row['offered_topic_id'],$row['offered_category_id']
				)
			);
		}
		return $topics;
	}

	public function fetchNeededTopics($userId)  {
		$stmt = $this->prepareStatement(_SQL_FETCH_NEEDED_TOPICS);
		
		$stmt->bindParam(':user_id', $userId, PDO::PARAM_INT);
		$stmt->execute();
	
		
		$topics = array();
		$rows = $stmt->fetchAll();
		foreach($rows as $row) {
			array_push($topics,
				new NeededTopic(
					$row['topic_id'], $row['user_id'], $row['title'], $row['created'], $row['vote_cnt'], ($row['yet_voted']==1?true:false),
					$row['needed_topic_id'],$row['needed_category_id']
				)
			);
		}
		return $topics;
	}
	
	
	

	public function insertOfferedTopic($userId, $title, $created, $offeredCategoryId) {
		$lastTopicId = $this->insertTopic($userId, $title, $created);
		
		$stmt = $this->prepareStatement(_SQL_INSERT_OFFERED_TOPIC);
		$stmt->bindParam(':topic_id', $lastTopicId, PDO::PARAM_INT);
		$stmt->bindParam(':offered_category_id', $offeredCategoryId, PDO::PARAM_INT);
		
		$stmt->execute();
		$lastOfferedTopicId = $this->lastInsertId();
		
		return new OfferedTopic($lastTopicId, $userId, $title, $created, 0, false, $lastOfferedTopicId, $offeredCategoryId);
	}
	
	public function insertNeededTopic($userId, $title, $created, $neededCategoryId) {
		$lastTopicId = $this->insertTopic($userId, $title, $created);
	
		$stmt = $this->prepareStatement(_SQL_INSERT_NEEDED_TOPIC);
		$stmt->bindParam(':topic_id', $lastTopicId, PDO::PARAM_INT);
		$stmt->bindParam(':needed_category_id', $neededCategoryId, PDO::PARAM_INT);
		$stmt->execute();
		
		$lastNeededTopicId = $this->lastInsertId();
		
		return new NeededTopic($lastTopicId, $userId, $title, $created, 0, false, $lastNeededTopicId, $neededCategoryId);
	}
	
	private function insertTopic($userId, $title, $created) {
		$stmt = $this->prepareStatement(_SQL_INSERT_TOPIC);
		
		$stmt->bindParam(':user_id', $userId, PDO::PARAM_INT);
		$stmt->bindParam(':title', $title, PDO::PARAM_STR);
		$stmt->bindParam(':created', unixToMySQL($created), PDO::PARAM_STR);
		
		$stmt->execute();
		
		return $this->lastInsertId();
	}
}
?>